package data.userdata;

import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import po.FacultyTeacherPO;
import po.Institute;
import po.RegisteryTeacherPO;
import po.StudentPO;
import dataservice.userdataservice.StudentDataService;

public class StudentData extends UnicastRemoteObject implements
		StudentDataService {

	PreparedStatement ps = null;
	Connection ct = null;
	ResultSet rs = null;

	public StudentData() throws RemoteException {
	}

	@Override
	public void init() {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			ct = DriverManager.getConnection("jdbc:odbc:CourseSelectionSystem");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	@Override
	public void finish() {
		try {
			if (ps != null) {
				ps.close();
			}
			if (ct != null) {
				ct.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	@SuppressWarnings("finally")
	@Override
	public boolean insert(StudentPO po) {
		init();
		try {
			String sql = "select id from Student where id='" + po.getId() + "'";
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				if (rs != null) {
					rs.close();
				}
				finish();
				return false;
			}
			if (rs != null) {
				rs.close();
			}
			sql = "insert into Student values('" + po.getId() + "','"
					+ po.getName() + "','" + po.getPassword() + "',"
					+ po.getYear() + ",'" + po.getInstitute().toString() + "',"
					+ po.getGpa() + ")";
			ps = ct.prepareStatement(sql);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			finish();

		}
		return true;

	}

	@Override
	public boolean delete(String id) {
		init();
		try {
			String sql = "delete from Student where id='" + id + "'";
			ps = ct.prepareStatement(sql);
			int i = ps.executeUpdate();
			finish();
			if (i == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean update(StudentPO po) {
		init();
		try {
			String sql = "update Student set name='" + po.getName()
					+ "', password='" + po.getPassword() + "', year="
					+ po.getYear() + ", institute='"
					+ po.getInstitute().toString() + "', gpa=" + po.getGpa()
					+ " where id='" + po.getId() + "'";
			ps = ct.prepareStatement(sql);
			int i = ps.executeUpdate();
			finish();
			if (i == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public StudentPO find(String id) {
		init();
		StudentPO po = null;

		try {
			String sql = "select * from Student where id='" + id + "'";
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				po = new StudentPO(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), Institute.toEnum(rs
								.getString(5)), rs.getDouble(6));
			}

			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			finish();
			return null;
		}
		finish();
		return po;
	}

	@Override
	public ArrayList<StudentPO> find(Institute institute) {
		ArrayList<StudentPO> list = new ArrayList<StudentPO>();
		init();
		try {
			String sql = "select * from Student where institute='" + institute
					+ "' order by id";
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				StudentPO po = new StudentPO(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), Institute.toEnum(rs
								.getString(5)), rs.getDouble(6));
				list.add(po);
			}

			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			finish();
			return null;
		}
		finish();
		return list;
	}

	@Override
	public ArrayList<StudentPO> find(Institute institute, int year)
			throws RemoteException {
		ArrayList<StudentPO> list = new ArrayList<StudentPO>();
		init();
		try {
			String sql = "select * from Student where institute='" + institute
					+ "' and year=" + year + " order by id";
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				StudentPO po = new StudentPO(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), Institute.toEnum(rs
								.getString(5)), rs.getDouble(6));
				list.add(po);
			}

			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			finish();
			return null;
		}
		finish();
		return list;
	}

}
